package com.maxd.respository.order;

import com.maxd.model.PublisherOrderDtoBean;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface OrderRepository extends Repository<PublisherOrderDtoBean, Long> {

    @Query(value = "from PublisherOrderDtoBean s where s.relation_id=:relation_id order by id desc")
    Page<PublisherOrderDtoBean> findByRelationId(@Param("relation_id") Long relation_id, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.trade_parent_id=:trade_parent_id")
    PublisherOrderDtoBean findByTradeParentId(@Param("trade_parent_id") String trade_parent_id);

    @Query(value = "select * from publisher_order_dto_bean b where b.relation_id = (select relation_id from " +
            "user_info where id=:userId) and b.relation_id!=0 and b.relation_id is not null",
            countQuery = "select count(*) from publisher_order_dto_bean b where b.relation_id = (select relation_id from " +
                    "user_info where id=:userId) and b.relation_id!=0 and b.relation_id is not null",
            nativeQuery = true)
    Page<PublisherOrderDtoBean> selectOrderListByUserId(@Param("userId") Long userId, Pageable pageable);

    @Query(value = "select u.*,ifnull(b.num,0) as num from  (select count(*) as num,b.relation_id from publisher_order_dto_bean b where b.relation_id !=0 GROUP BY b.relation_id) b " +
            "left join user_info u " +
            "on u.relation_id=b.relation_id " +
            "where if(:phone!=-1,u.phone=:phone,1=1) " +
            "and if(:nickName!=-1,u.nick_name=:nickName,1=1) " +
            "and if(:gradeIndex!=-1,u.grade_index=:gradeIndex,1=1) " +
            "and if(:relationId!=-1,u.relation_id=:relationId,1=1) " +
            "order by b.num desc",
            countQuery = "select count(*) from  (select count(*) as num,b.relation_id from publisher_order_dto_bean b where b.relation_id !=0 GROUP BY b.relation_id) b " +
                    " left join user_info u on u.relation_id=b.relation_id "+
                    "where if(:phone!=-1,u.phone=:phone,1=1) " +
                    "and if(:nickName!=-1,u.nick_name=:nickName,1=1) " +
                    "and if(:gradeIndex!=-1,u.grade_index=:gradeIndex,1=1) " +
                    "and if(:relationId!=-1,u.relation_id=:relationId,1=1) "
            ,nativeQuery = true)
    Page<Map<String,Object>> selectOrderList(Pageable pageable,String phone,String nickName,String relationId,String gradeIndex);

    @Query(value = "select u.*,ifnull(b.num,0) as num from  (select count(*) as num,b.relation_id from publisher_order_dto_bean b where b.relation_id !=0 GROUP BY b.relation_id) b " +
            "left join user_info u " +
            "on u.relation_id=b.relation_id " +
            "where if(:phone!=-1,u.phone=:phone,1=1) " +
            "and if(:nickName!=-1,u.nick_name=:nickName,1=1) " +
            "and if(:relationId!=-1,u.relation_id=:relationId,1=1) " +
            "order by b.num desc",nativeQuery = true)
    List<Map<String,Object>> selectOrderLists(String phone,String nickName,String relationId);

    @Query(value = "from PublisherOrderDtoBean s where s.relation_id=:relation_id and s.tk_status=:tk_status order by id desc")
    Page<PublisherOrderDtoBean> findByRelationIdAndStatus(@Param("relation_id") Long relation_id, @Param("tk_status") Integer tk_status, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.fromInfo=:fromInfo order by id desc")
    Page<PublisherOrderDtoBean> findByFromAndStatus(@Param("tk_status") Integer tk_status, @Param("fromInfo") String fromInfo, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.tk_status=:tk_status order by id desc")
    Page<PublisherOrderDtoBean> findByStatus(@Param("tk_status") Integer tk_status, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.relation_id=:relation_id order by id desc")
    Page<PublisherOrderDtoBean> findByStatusAndRelation(@Param("tk_status") Integer tk_status, @Param("relation_id") Long relation_id, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.fromInfo=:fromInfo and s.relation_id=:relation_id order by id desc")
    Page<PublisherOrderDtoBean> findByFromAndRelation(@Param("fromInfo") String fromInfo, @Param("relation_id") Long relation_id, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.relation_id=:relation_id order by id desc")
    Page<PublisherOrderDtoBean> findByRelation(@Param("relation_id") Long relation_id, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.fromInfo=:fromInfo and s.relation_id=:relation_id and s.tk_status=:tk_status order by id desc")
    Page<PublisherOrderDtoBean> findByFromAndRelationAndStatus(@Param("tk_status") Integer tk_status, @Param("fromInfo") String fromInfo, @Param("relation_id") Long relation_id, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where (:fromInfo = 'all' or s.fromInfo = :fromInfo) and s.invitation=:invitation and s.tk_status=:tk_status order by id desc")
    Page<PublisherOrderDtoBean> findByFromAndInvitationAndStatus(@Param("tk_status") Integer tk_status, @Param("fromInfo") String fromInfo, @Param("invitation") String invitation, Pageable pageable);

    @Query(value = "select * from publisher_order_dto_bean s where (:fromInfo = 'all' or s.from_info = :fromInfo) and s.tk_status=:tk_status and s.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null) order by id desc",nativeQuery = true)
    Page<PublisherOrderDtoBean> findByFromAndInvitationAndStatuss(@Param("tk_status") Integer tk_status, @Param("fromInfo") String fromInfo, @Param("invitation") String invitation, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where  (:fromInfo = 'all' or s.fromInfo = :fromInfo) and s.invitation=:invitation order by id desc")
    Page<PublisherOrderDtoBean> findByFromAndInvitation( @Param("fromInfo") String fromInfo, @Param("invitation") String invitation, Pageable pageable);

    @Query(value = "select * from publisher_order_dto_bean s where (:fromInfo = 'all' or s.from_info = :fromInfo) and  s.invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null) order by id desc",nativeQuery = true)
    Page<PublisherOrderDtoBean> findByFromAndInvitations( @Param("fromInfo") String fromInfo, @Param("invitation") String invitation, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.fromInfo=:fromInfo order by id desc")
    Page<PublisherOrderDtoBean> findByFrom(@Param("fromInfo") String fromInfo, Pageable pageable);

    @Query(value = "from PublisherOrderDtoBean s where s.invitation=:relation_id and s.tk_status=:tk_status order by id desc")
    Page<PublisherOrderDtoBean> findByInvitationAndStatus(@Param("relation_id") String relation_id, @Param("tk_status") Integer tk_status, Pageable pageable);

    @Query(value = "select sum(t.invitationMoney) from PublisherOrderDtoBean t where t.invitation=:relation_id and t.tk_status=:tk_status and t.tk_paid_time like concat('%',:time,'%')")
    String pubShareInvitationPreFeeCount(@Param("relation_id") String relation_id, @Param("tk_status") int tk_status, @Param("time") String time);

    @Query(value = "select sum(t.pub_share_pre_fee_user) from PublisherOrderDtoBean t where t.relation_id=:relation_id and t.tk_status=:tk_status and t.tk_paid_time like concat('%',:time,'%')")
    String pubSharePreFeeCount(@Param("relation_id") Long relation_id, @Param("tk_status") int tk_status, @Param("time") String time);

    @Query(value = "select count(t.trade_id) from PublisherOrderDtoBean t where t.relation_id=:relation_id and t.tk_paid_time like concat('%',:time,'%')")
    String getPaidCount(@Param("relation_id") Long relation_id, @Param("time") String time);

    @Query(value = "select count(t.trade_id) from PublisherOrderDtoBean t where t.relation_id=:relation_id")
    String isNewsUser(@Param("relation_id") Long relation_id);

    @Query(value = "from PublisherOrderDtoBean s where s.trade_id=:trade_id")
    PublisherOrderDtoBean findByRelationIdCount(@Param("trade_id") String trade_id);

    @Query(value = "from PublisherOrderDtoBean s where s.trade_id=:trade_id")
    List<PublisherOrderDtoBean> findByRelationIdCountList(@Param("trade_id") String trade_id);

    @Query(value = "from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%') ")
    List<PublisherOrderDtoBean> findByTime(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select sum(s.pub_share_pre_fee_user) from PublisherOrderDtoBean s where s.relation_id=:relation_id and s.balance=:balance and s.tk_status=3 and s.tk_paid_time like concat('%',:time,'%') ")
    String countByTime(@Param("time") String time, @Param("relation_id") Long relation_id, @Param("balance") int balance);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.invitation=:relation_id and s.balance=:balance and s.tk_status=3 and s.tk_paid_time like concat('%',:time,'%') ")
    String countByTnvitationMoneyTime(@Param("time") String time, @Param("relation_id") String relation_id, @Param("balance") int balance);

    @Query(value = "select sum(s.invitationMoneys) from PublisherOrderDtoBean s where s.invitations=:relation_id and s.balance=:balance and s.tk_status=3 and s.tk_paid_time like concat('%',:time,'%') ")
    String countByTnvitationMoneyTimes(@Param("time") String time, @Param("relation_id") String relation_id, @Param("balance") int balance);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.invitation=:invitation and s.tk_status=3")
    String countByTnvitationMoney(@Param("invitation") String invitation);

    @Query(value = "select sum(s.invitationMoneys) from PublisherOrderDtoBean s where s.invitations=:invitation and s.tk_status=3")
    String countByTnvitationMoneys(@Param("invitation") String invitation);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.invitation=:invitation and s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%')")
    String countByTnvitationAndTimeMoney(@Param("invitation") String invitation, @Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select sum(s.invitationMoneys) from PublisherOrderDtoBean s where s.invitations=:invitation and s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%')")
    String countByTnvitationAndTimeMoneys(@Param("invitation") String invitation, @Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%')")
    String countByTimeMoney(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.fromInfo=:from and s.tk_paid_time like concat('%',:time,'%')")
    String countByTimeMoneyAndFromInfo(@Param("time") String time, @Param("tk_status") int tk_status, @Param("from") String from);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.relation_id=:relation_id and s.tk_status=3")
    String countByRelationMoney(@Param("relation_id") Long relation_id);

    @Query(value = "from PublisherOrderDtoBean s where s.relation_id=:relation_id and s.balance=:balance and s.tk_status=3 and s.tk_paid_time like concat('%',:time,'%') ")
    List<PublisherOrderDtoBean> getListByTime(@Param("time") String time, @Param("relation_id") Long relation_id, @Param("balance") int balance);


    @Query(value = "select sum(s.pub_share_pre_fee) from PublisherOrderDtoBean s where  s.balance=:balance and s.tk_status=:tk_status")
    String getTotal(@Param("tk_status") int tk_status, @Param("balance") int balance);

    @Query(value = "select sum(s.pub_share_pre_fee) from PublisherOrderDtoBean s where  s.balance=:balance and s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%') ")
    String getTotalByTime(@Param("time") String time, @Param("tk_status") int tk_status, @Param("balance") int balance);

    @Query(value = "select count(t.trade_id) from PublisherOrderDtoBean t where t.tk_paid_time like concat('%',:time,'%')")
    String getPaidCountNoRelation(@Param("time") String time);

    @Query(value = "select sum(s.pub_share_pre_fee) from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%') ")
    String getTotalByTimes(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select sum(s.pub_share_pre_fee) from PublisherOrderDtoBean s where s.tk_status=:tk_status")
    String getTotals(@Param("tk_status") int tk_status);

//    @Query(value = "select s.relation_id from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.tk_paid_time like concat('%',:time,'%') group by s.relation_id")
//    List<Long> findRelationList(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "select s.relation_id from publisher_order_dto_bean s where s.tk_status=:tk_status and s.relation_id !=0 and s.tk_paid_time like concat('%',:time,'%') group by s.relation_id",nativeQuery = true)
    List<Long> findRelationList(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "SELECT " +
            "sum( invitation_money ) as money,invitation " +
            "FROM publisher_order_dto_bean " +
            "WHERE tk_status = :tk_status AND tk_paid_time LIKE concat( '%', :time, '%' ) " +
            "and invitation is not null and balance=0 " +
            "GROUP BY invitation",nativeQuery = true)
    List<Map<String,Object>> selectInvitationMoney(@Param("time") String time, @Param("tk_status") int tk_status);
    @Query(value = "SELECT " +
            "sum( top_invitation_money ) as money,top_relation_id " +
            "FROM publisher_order_dto_bean " +
            "WHERE tk_status = :tk_status AND tk_paid_time LIKE concat( '%', :time, '%' ) " +
            "and top_relation_id is not null and balance=0 " +
            "GROUP BY top_relation_id",nativeQuery = true)
    List<Map<String,Object>> selectTopInvitationMoney(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "SELECT " +
            "sum( pub_share_pre_fee_user ) as money ,relation_id " +
            "FROM publisher_order_dto_bean " +
            "WHERE tk_status = :tk_status AND tk_paid_time LIKE concat( '%', :time, '%' ) " +
            "and relation_id !=0 and balance=0 " +
            "GROUP BY relation_id",nativeQuery = true)
    List<Map<String,Object>> selectRelationIdMoney(@Param("time") String time, @Param("tk_status") int tk_status);

    @Query(value = "SELECT " +
            "sum( invitation_moneys ) as money,invitations " +
            "FROM publisher_order_dto_bean " +
            "WHERE tk_status = :tk_status AND tk_paid_time LIKE concat( '%', :time, '%' ) " +
            "and invitations is not null and balance=0 " +
            "GROUP BY invitations",nativeQuery = true)
    List<Map<String,Object>> selectInvitationMoneys(@Param("time") String time, @Param("tk_status") int tk_status);

    @Transactional
    @Modifying
    @Query(value = "update publisher_order_dto_bean set balance=:balance where tk_status = :tk_status AND tk_paid_time LIKE concat( '%', :time, '%' ) and relation_id !=0 ",nativeQuery = true)
    int updateOrderBalance(@Param("time") String time, @Param("tk_status") int tk_status,@Param("balance") int balance);

    @Query(value = "select s.invitation from PublisherOrderDtoBean s where s.tk_paid_time like concat('%',:time,'%') group by s.invitation")
    List<Long> findInvitationList(@Param("time") String time);

    @Query(value = "from PublisherOrderDtoBean s where s.tk_paid_time like concat('%',:time,'%')")
    List<PublisherOrderDtoBean> findGoodsList(@Param("time") String time);

    @Query(value = "select count(s.id) from PublisherOrderDtoBean s where s.item_id=:item_id and s.tk_paid_time like concat('%',:time,'%')")
    Long countGoodsByItem(@Param("time") String time, @Param("item_id") String item_id);


    @Query(value = "select count(s.relation_id) from PublisherOrderDtoBean s where s.item_id=:item_id and s.tk_paid_time like concat('%',:time,'%')")
    Long findGoodsCountOrderByRelation_id(@Param("time") String time, @Param("item_id") String item_id);

    @Query(value = "select sum(s.invitationMoney) from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.invitation=:invitation and s.tk_paid_time like concat('%',:time,'%')")
    String findInvitationCountMoney(@Param("time") String time, @Param("tk_status") int tk_status, @Param("invitation") String invitation);

    @Query(value = "select count(s.id) from PublisherOrderDtoBean s where s.tk_status=:tk_status and s.invitation=:invitation and s.tk_paid_time like concat('%',:time,'%')")
    Long countAllByInvitation(@Param("time") String time, @Param("tk_status") int tk_status, @Param("invitation") String invitation);

    @Query(value = "select count(*) as counts,from_info as fromInfo,sum(pub_share_pre_fee_user) as money from publisher_order_dto_bean " +
            "where relation_id=:relationId and tk_status in (3,12,14) " +
            "and if(:day!=1,date_format(tk_paid_time,'%Y-%m')=date_format(:time,'%Y-%m'),date_format(tk_paid_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')) "+
            "GROUP BY from_info",nativeQuery = true)
    List<Map<String,Object>> selectOrderCountByRelationIdList(@Param("relationId") String relationId, @Param("day") Integer day, @Param("time") Date time);

    @Query(value = "select sum(count) as counts,fromInfo,sum(money) as money from (select count(*) as `count`,from_info as fromInfo,sum(invitation_money) as money from publisher_order_dto_bean " +
            "where invitation=:relationId and tk_status in (3,12,14) " +
            "and if(:day!=1,date_format(tk_paid_time,'%Y-%m')=date_format(:time,'%Y-%m'),date_format(tk_paid_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')) "+
            "GROUP BY from_info " +
            "union ALL " +
            "select count(*) as  `count`,from_info as fromInfo,sum(invitation_moneys) as money from publisher_order_dto_bean " +
            "where invitations=:relationId and  tk_status in (3,12,14) " +
            "and if(:day!=1,date_format(tk_paid_time,'%Y-%m')=date_format(:time,'%Y-%m'),date_format(tk_paid_time,'%Y-%m-%d')=date_format(:time,'%Y-%m-%d')) "+
            "GROUP BY from_info " +
            ") a GROUP BY fromInfo",nativeQuery = true)
    List<Map<String,Object>> selectOrderCountByInvitationIdList(@Param("relationId") String relationId,@Param("day") Integer day,@Param("time") Date time);

    @Query(value = "select sum(money) as money from (select sum(invitation_money) as money from publisher_order_dto_bean " +
            "where invitation=:relationId and balance=1 " +
            "union ALL " +
            "select sum(invitation_moneys) as money from publisher_order_dto_bean " +
            "where invitations=:relationId and  balance=1 " +
            "union ALL " +
            "select sum(pub_share_pre_fee_user) as money from publisher_order_dto_bean " +
            "where relation_id=:relationId and balance=1 " +
            ") a ",nativeQuery = true)
    Double selectOrderCountByInvitationIdList(@Param("relationId") String relationId);


    @Query(value = "select * from publisher_order_dto_bean where if(:balance!=-1,balance=:balance,1=1) " +
            "and if(:invitation!=-1,invitation=:invitation,1=1) " +
            "and if(:relation_id!=-1,relation_id=:relation_id,1=1) " +
            "and if(:tk_status!=-1,tk_status=:tk_status,1=1) " +
            "and if(:from!='all',from_info=:from,1=1) order by id desc ",nativeQuery = true)
    List<PublisherOrderDtoBean> selectOrderList(int balance,String invitation,Long relation_id,int tk_status,String from);

    @Query(value = "select count(*) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and relation_id=:relationId ",nativeQuery = true)
    Integer selectOrdersCountByTimeAndRelationId (@Param("time") String time,String relationId);


    @Query(value = "select count(*) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitation=:relationId ",nativeQuery = true)
    Integer selectOrdersCountByTimeAndInvitation(@Param("time") String time,String relationId);

    @Query(value = "select count(*) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitations=:relationId ",nativeQuery = true)
    Integer selectOrdersCountByTimeAndInvitations(@Param("time") String time,String relationId);

    @Query(value = "select ifnull(sum(alipay_total_price),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and relation_id=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersPriceByTimeAndRelationId (@Param("time") String time, String relationId);

    @Query(value = "select ifnull(sum(alipay_total_price),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitation=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersPriceByTimeAndInvitation(@Param("time") String time,String relationId);

    @Query(value = "select ifnull(sum(alipay_total_price),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitations=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersPriceByTimeAndInvitations(@Param("time") String time,String relationId);

    @Query(value = "select ifnull(sum(pub_share_pre_fee_user),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and relation_id=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersMoneyByTimeAndRelationId (@Param("time") String time, String relationId);

    @Query(value = "select ifnull(sum(invitation_money),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitation=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersMoneyByTimeAndInvitation(@Param("time") String time,String relationId);

    @Query(value = "select ifnull(sum(invitation_moneys),0.00) from publisher_order_dto_bean where " +
            "tk_status in (3,12) " +
            "and tk_paid_time like concat('%',:time,'%') " +
            "and invitations=:relationId ",nativeQuery = true)
    BigDecimal selectOrdersMoneyByTimeAndInvitations(@Param("time") String time,String relationId);



}